#_*_encoding : utf-8 _*_
'''
Created on 2017年5月31日

@author: lingxiang
'''
import pymysql


'''增加一条数据'''
def  add_new_proxy(proxy):
    try:
        conn= pymysql.connect(host='zj8692286.lw456.com',user='zj8692286',passwd='8692286',db='zj8692286',port=3306)
        cur=conn.cursor()
        
        if len(proxy)==0:
            return
        sql = 'INSERT INTO proxy (url,score,status,isOk) VALUES (%s,%s,%s,%s)'
        row=cur.execute(sql, (proxy, 0,0,0))
        if  row==1:
            print ("插入成功")
        conn.commit()
        cur.close()
        conn.close()
    except Exception:
        print ("插入失败")
        
'''增加多条数据''' 
def  add_new_proxys(proxys):
    try:
        conn= pymysql.connect(host='zj8692286.lw456.com',user='zj8692286',passwd='8692286',db='zj8692286',port=3306)
        cur=conn.cursor()
        
        if len(proxys)==0:
            return
        sql="insert into proxy (url,score,status,isOk) values "
        for proxy in proxys :
            sql+="('"+proxy+"',0,0,0)," 
        sql=sql[0:len(sql)-1]
        print (cur.execute(sql))
        conn.commit()
        cur.close()
        conn.close()
    except Exception:
        print ("插入失败")
        
 
'''查询到所有代理IP 未检测的  500条'''
def select_proxy_all(size=500):  
    conn = pymysql.connect(host='zj8692286.lw456.com',user='zj8692286',passwd='8692286',db='zj8692286',port=3306) 
    proxy_all=[] 
   
    cur = conn.cursor()  
    cur.execute("SELECT * FROM proxy where status = 0 limit 0,"+str(size))  
    for r in cur:
        proxy_one={}  
        proxy_one["url"]=str(r[0]) 
        proxy_one["score"]=str(r[1])
        proxy_one["status"]=str(r[2]) 
        proxy_one["isOk"]=str(r[3])
        proxy_all.append(proxy_one)
    cur.close()      
    conn.close()
    return proxy_all
    
'''随机获取一条可用的代理IP数据 '''
def select_one_proxy():
    conn = pymysql.connect(host='zj8692286.lw456.com',user='zj8692286',passwd='8692286',db='zj8692286',port=3306) 
    cur = conn.cursor()  
    cur.execute("select * from proxy where isOk=1 order by rand() LIMIT 1 ")
    for r in cur:
        proxy_one={}  
        proxy_one["url"]=str(r[0]) 
    cur.close()      
    conn.close()
    return proxy_one["url"]
    
'''查询多个可用的代理IP   '''
def select_proxy_use(size):  
    conn = pymysql.connect(host='zj8692286.lw456.com',user='zj8692286',passwd='8692286',db='zj8692286',port=3306) 
    proxy_all=[] 
    cur = conn.cursor()
    if size == 0 or size == None:
        size=100  
    cur.execute("SELECT * FROM proxy where isOk=1 limit 0, %s"%size)  
    for r in cur:
        proxy_one={}  
        proxy_one["url"]=str(r[0]) 
        proxy_one["score"]=str(r[1])
        proxy_one["status"]=str(r[2]) 
        proxy_one["isOk"]=str(r[3])
        proxy_all.append(proxy_one)
    cur.close()      
    conn.close()
    return proxy_all
    
'''验证之后    根据url修改  score status  isOk'''
def update_proxy(url,name_list,val_list):  
    conn = pymysql.connect(host='zj8692286.lw456.com',user='zj8692286',passwd='8692286',db='zj8692286',port=3306)  
    cur = conn.cursor() 
    sql=""
    if len(name_list)==1:      
        sql="UPDATE proxy SET "+name_list[0]+"="+val_list[0]+" WHERE url='"+url+"'"
    elif len(name_list)==2:      
        sql="UPDATE proxy SET "+name_list[0]+"="+val_list[0]+","+name_list[1]+"="+val_list[1]+" WHERE url='"+url+"'"
    elif len(name_list)==3:      
        sql="UPDATE proxy SET "+name_list[0]+"="+val_list[0]+","+name_list[1]+"="+val_list[1]+","+name_list[2]+"="+val_list[2]+" WHERE url='"+url+"'" 
    sta=cur.execute(sql) 
    conn.commit()
    if sta==1: 
        print('更新成功')
        return 1 
    else: 
        print('更新失败')
        return 0    
    cur.close()     
    conn.close() 
    
 
'''多个或单个条件删除数据'''
def delete_proxy(names,vals): 
    conn = pymysql.connect(host='zj8692286.lw456.com',user='zj8692286',passwd='8692286',db='zj8692286',port=3306) 
    cur = conn.cursor()
    
    if len(names)==1:
        sql = "DELETE FROM proxy WHERE "+names[0]+"='"+vals[0]+"'" 
    elif len(names)==2:
        sql = "DELETE FROM proxy WHERE "+names[0]+"='"+vals[0]+"' and " +names[1]+"="+vals[1]
    elif len(names)==3:
        sql = "DELETE FROM proxy WHERE "+names[0]+"="+vals[0]+" and " +names[1]+"="+vals[1]+" and " +names[2]+"="+vals[2]
    sta=cur.execute(sql)
    conn.commit()  
    if sta==1:  
        print('删除成功')
        return 1  
    else:  
        print('删除失败') 
        return 0    
  
    cur.close()      
    conn.close()  
    
    

